package fr.afcepf.ai89.esharing.data;

import java.util.Date;
import java.util.List;

import javax.ejb.Stateless;
import javax.persistence.EntityManager;
import javax.persistence.Id;
import javax.persistence.PersistenceContext;
import javax.persistence.Query;

import fr.afcepf.ai89.esharing.entity.Aem;
import fr.afcepf.ai89.esharing.entity.CategorieAem;
import fr.afcepf.ai89.esharing.entity.FrCommune;
import fr.afcepf.ai89.esharing.entity.FrDepartement;
import fr.afcepf.ai89.esharing.entity.FrRegion;
import fr.afcepf.ai89.esharing.entity.Indisponibilite;
import fr.afcepf.ai89.esharing.entity.Marque;
import fr.afcepf.ai89.esharing.entity.Proprietaire;
import fr.afcepf.ai89.esharing.entity.TypeAem;
import fr.afcepf.ai89.esharing.entity.Valeur;

@Stateless
public class DAOAem implements IDAOAem {

	
	@PersistenceContext(unitName = "ESharingPhase22")
	EntityManager em;
	
	@Override
	public List<Aem> getAems(String[] motsCles, FrRegion region,
			FrDepartement dept, FrCommune commune, Long codePostal,
			Date dateDebut, Date dateFin, CategorieAem categorie,
			Valeur valeur) {
		
//		Query hql = em.createQuery("SELECT a FROM Aem a "
//				+ "LEFT JOIN FETCH a.imgAems "
//				+ "INNER JOIN FETCH a.categorieAem "
//				+ "INNER JOIN FETCH a.typeAem "
//				+ "INNER JOIN FETCH a.marque "
//				+ "LEFT JOIN FETCH a.valeurs "
//				+ "LEFT JOIN FETCH a.indisponibilite "
//				+ "LEFT JOIN FETCH a.invisibilite "
//				+ "WHERE (a.proprietaire.frCodePostAsso.frCodePostalAssoPK.frDepartement.frRegion.idRegion = : param1 OR param1 IS NULL) "
//				+ "AND (a.proprietaire.frCodePostAsso.frCodePostalAssoPK.frDepartement.idDepartement = : param2 OR param2 IS NULL) "
//				+ "AND (a.proprietaire.frCodePostAsso.frCodePostalAssoPK.frCommune.idCommune = : param3 OR param3 IS NULL) "
//				+ "AND (a.proprietaire.frCodePostAsso.frCodePostalAssoPK.codePostal = : param4 OR param4 IS NULL) "
//				+ "AND (a.proprietaire.typeAem.categorieAem.idCategorieAem = : param5 OR param5 IS NULL) "
//				+ "AND (a.proprietaire.valeur.valeurJournaliere =< param6 OR param6 IS NULL) "
//				+ "AND ((SELECT COUNT (*) FROM a.indisponiblite WHERE(a.indisponiblite.dateDebutIndisponibilite < param7)"
//					+ "AND (a.indisponiblite.dateFinIndisponibilite > param8) "
//					+ "AND (a.indisponiblite.dateRetraitIndisponibilite IS NULL)) "
//				+ "AND ((SELECT COUNT (*) FROM a.invisibiliteAem WHERE(a.invisibiliteAem.dateDebutInvisibiliteAem < param9) "
//					+ "AND (a.invisibiliteAem.dateFinInvisibiliteAem >param10) "
//					+ "AND (a.invisibiliteAem.dateRetraitInvisibiliteAem IS NULL))"
//				);

		String maQuery1 = "SELECT a FROM Aem a "
						+ "INNER JOIN FETCH a.typeAem ta "
						+ "INNER JOIN FETCH a.marque "
						+ "INNER JOIN FETCH ta.categorieAem "
						+ "INNER JOIN FETCH a.proprietaire "
//						+ "LEFT JOIN a.imgAems "
//						+ "LEFT JOIN a.valeurs "
//						+ "LEFT JOIN a.indisponibilites "
//						+ "LEFT JOIN a.invisibiliteAems "
//						+ "LEFT JOIN a.emprunts "
//						+ "LEFT JOIN a.promotionAems "
						+ "WHERE a.dateRevocation IS NULL "
						+ "AND a.proprietaire.dateDepart IS NULL "
						+ "AND ((SELECT COUNT(*) "
							  + "FROM Bannissement b "
							  + "WHERE (b.proprietaire.idUtilisateur = a.proprietaire.idUtilisateur) "
							  + "AND (b.dateDebutBannissement < CURRENT_DATE) "
							  + "AND (b.dateFinBannissement > CURRENT_DATE) "
							  + "AND (b.dateRetraitBannissement IS NULL)) = 0)"
						+ "AND ((SELECT COUNT (*) "
							  + "FROM InvisibiliteProprietaire ip "
							  + "WHERE (ip.proprietaire.idUtilisateur = a.proprietaire.idUtilisateur) "
							  + "AND (ip.dateDebutInvisibiliteProp < CURRENT_DATE) "
							  + "AND (ip.dateFinInvisibiliteProp > CURRENT_DATE) "
							  + "AND (ip.dateRetraitInvisibiliteProp IS NULL)) = 0) "
						+ "AND ((SELECT COUNT (*) "
							  + "FROM InvisibiliteAem iv "
							  + "WHERE (iv.aem.idAem = a.idAem) "
							  + "AND (iv.debutInvisibiliteAem < CURRENT_DATE) "
							  + "AND (iv.finInvisibiliteAem > CURRENT_DATE) "
							  + "AND (iv.retraitInvisibiliteAem IS NULL)) = 0)";
		
		if (region != null) {
			
			maQuery1 = maQuery1 + " AND (a.proprietaire.frCodePostalCommune.frCommune.frDepartement.frRegion.idRegion = :param1)";
			
		}
		
		if (dept != null) {
			
			maQuery1 = maQuery1 + " AND (a.proprietaire.frCodePostalCommune.frCommune.frDepartement.idDepartement = :param2)";
			
		}
				
		if (commune != null) {
			
			maQuery1 = maQuery1 + " AND (a.proprietaire.frCodePostalCommune.frCommune.id.codeCommune = :param3)";
			
		}
				
		if (codePostal != null && codePostal > 0) {
			
			maQuery1 = maQuery1 + " AND (a.proprietaire.frCodePostalCommune.id.codePostal = :param4)";
			
		}
				
		if (categorie != null) {
			
			maQuery1 = maQuery1 + " AND (a.typeAem.categorieAem.idCategorieAem = :param5)";
			
		}
				
		if (valeur != null) {
			
			maQuery1 = maQuery1 + " AND ((SELECT MAX(valeurJournaliere) "
								 	   + "FROM Valeur v "
								 	   + "WHERE v.aem.idAem = a.idAem ) <= :param6) "
								  + "OR ((SELECT COUNT(*) "
								  	   + "FROM Valeur v "
								  	   + "WHERE v.aem.idAem = a.idAem) = 0))";
			
		}
		
		if (dateDebut != null && dateFin != null) {
			
			//System.out.println("*****Null date attempt failed*****");
			
			maQuery1 = maQuery1 + " AND ((SELECT COUNT (*) "
									   + "FROM Indisponibilite ind " 
									   + "WHERE (ind.aem.idAem = a.idAem) "
									    + "AND ((ind.dateDebutIndisponibiliteAem < :param8) "
										  + "OR (ind.dateFinIndisponibiliteAem > :param7)) "
										 + "AND (ind.dateRetraitIndisponibiliteAem IS NULL)) = 0)"
								+ " AND ((SELECT COUNT (*) "
									   + "FROM Emprunt empt " 
									   + "WHERE (empt.aem.idAem = a.idAem) "
									    + "AND ((empt.dateDebut < :param8) "
									      + "OR (empt.dateFin > :param7)) "
									     + "AND (empt.dateRestitution IS NULL) "
									     + "AND (empt.dateAnnulation IS NULL) "
									     + "AND (empt.dateValidation IS NOT NULL)) = 0)";
			
		}
		else {
			
			//System.out.println("*****Null date attempt succeeded*****");
			
		}
				
		String maQuery2 = "";
		int indiceParam = 9;
		
		if (motsCles.length > 0) {
		
			for (String s : motsCles) {
				
				//System.out.println("*****Recherche String : " + s + "*****");
				
				maQuery2 = maQuery2 + " AND (LOWER(a.marque.marque) LIKE :param" + String.valueOf(indiceParam) 
									+ " OR LOWER(a.typeAem.typeAem) LIKE :param" + String.valueOf(indiceParam) 
									+ " OR LOWER(a.descAem) LIKE :param" + String.valueOf(indiceParam) +")";
				indiceParam++;
			}
		
		}
		
		String queryString = maQuery1 + maQuery2;
		//System.out.println(queryString);
		Query hql = em.createQuery(queryString);
		
		if (region != null) {
			
			hql.setParameter("param1", region.getIdRegion());
			
		}
		if (dept != null) {
			
		    hql.setParameter("param2",dept.getIdDepartement());
		    
		}
		if (commune != null) {
			
			hql.setParameter("param3",commune.getId().getCodeCommune());
			
		}
		if (codePostal != null && codePostal > 0) {
			
			hql.setParameter("param4",codePostal);
			
		}
		if (categorie != null) {
			
			hql.setParameter("param5",categorie.getIdCategorieAem());
			
		}
		if (valeur != null) {
			
			hql.setParameter("param6",valeur.getValeurJournaliere());
			
		}
		if (dateDebut != null && dateFin != null ) {
			
			hql.setParameter("param7",dateDebut);
			hql.setParameter("param8",dateFin);
			
		}
		
		int novoIndice = 9;
		
		if (motsCles.length > 0) {
		
			for (String s : motsCles) {
				
				hql.setParameter("param" + String.valueOf(novoIndice), "%" + s + "%");
				novoIndice++;
			}
			
		}

		// Il faut un Dynamic Query Builder qui précise WHERE a.marque.marque LIKE x OR a.typeAem.typeAem LIKE x OR a.desc
		// ensuite en générant une ligne de ça pour chaque mot clé x dans motsCles
		
		List<Aem> listeAemsRecherche = (List<Aem>)hql.getResultList();
			
		return listeAemsRecherche;
		
	}

	@Override
	public Aem getAem(int idAem) {
		
		Query hql = em.createQuery("SELECT a FROM Aem a "
								 + "INNER JOIN FETCH a.typeAem ta "
								 + "INNER JOIN FETCH a.marque "
								 + "INNER JOIN FETCH ta.categorieAem "
								 + "INNER JOIN FETCH a.proprietaire "
//								 + "LEFT JOIN a.imgAems "
//								 + "LEFT JOIN a.valeurs "
//								 + "LEFT JOIN a.indisponibilites "
//								 + "LEFT JOIN a.invisibiliteAems "
//								 + "LEFT JOIN a.emprunts "
//								 + "LEFT JOIN a.promotionAems "
								 + "WHERE a.idAem = :paramIdAem");
				
		hql.setParameter("paramIdAem",idAem);
		Aem ficheAem = (Aem)hql.getSingleResult();
		return ficheAem;
		
	}
	
	@Override
	public List<Aem> getAems() {
		
		Query hql = em.createQuery("SELECT a FROM Aem a "
								 + "INNER JOIN FETCH a.typeAem ta "
								 + "INNER JOIN FETCH a.marque "
								 + "INNER JOIN FETCH ta.categorieAem "
								 + "INNER JOIN FETCH a.proprietaire "
								 + "LEFT JOIN a.imgAems "
								 + "LEFT JOIN a.valeurs "
								 + "LEFT JOIN a.indisponibilites "
								 + "LEFT JOIN a.invisibiliteAems "
								 + "LEFT JOIN a.promotionAems");
				
		List<Aem> aems = (List<Aem>)hql.getResultList();
		return aems;
		
	}

	@Override
	public List<Aem> getAems(String[] motsCles) {
		String maQuery1 = "SELECT a FROM Aem a "
						+ "INNER JOIN FETCH a.typeAem ta "
						+ "INNER JOIN FETCH a.marque "
						+ "INNER JOIN FETCH ta.categorieAem "
						+ "INNER JOIN FETCH a.proprietaire "
		//				+ "LEFT JOIN a.imgAems "
		//				+ "LEFT JOIN a.valeurs "
		//				+ "LEFT JOIN a.indisponibilites "
		//				+ "LEFT JOIN a.invisibiliteAems "
		//				+ "LEFT JOIN a.emprunts "
		//				+ "LEFT JOIN a.promotionAems "
						+ "WHERE a.dateRevocation IS NULL "
						+ "AND a.proprietaire.dateDepart IS NULL "
						+ "AND ((SELECT COUNT(*) "
							   + "FROM Bannissement b "
							   + "WHERE b.idProprietaire = a.proprietaire.idProprietaire "
							   + "AND (b.dateDebutBannissement < CURRENT_DATE) "
							   + "AND (b.dateFinBannissement > CURRENT_DATE) "
							   + "AND (b.dateRetraitBannissement IS NULL)) = 0)"
						+ "AND ((SELECT COUNT (*) "
							   + "FROM InvisibiliteAem iv "
							   + "WHERE (iv.aem.idAem = a.idAem) "
							   + "AND (iv.debutInvisibiliteAem < CURRENT_DATE) "
							   + "AND (iv.finInvisibiliteAem > CURRENT_DATE) "
							   + "AND (iv.retraitInvisibiliteAem IS NULL)) = 0)";
				
		String maQuery2 = new String();
		int indiceParam = 9;
		
		for (String s : motsCles) {
			
			maQuery2 = maQuery2 + " AND (LOWER(a.marque.marque) LIKE :param" + String.valueOf(indiceParam) + " OR LOWER(a.typeAem.typeAem) LIKE :param" + String.valueOf(indiceParam) + " OR LOWER(a.descAem) LIKE :param" + String.valueOf(indiceParam) +")";
			indiceParam++;
		}
		
		Query hql = em.createQuery(maQuery1 + maQuery2);
		
		int novoIndice = 1;
		
		for (String s : motsCles) {
			
			hql.setParameter("param" + String.valueOf(novoIndice), "%" + s + "%");
			novoIndice++;
		}

		// Il faut un Dynamic Query Builder qui précise WHERE a.marque.marque LIKE x OR a.typeAem.typeAem LIKE x OR a.desc
		// ensuite en générant une ligne de ça pour chaque mot clé x dans motsCles
		
		List<Aem> listeAemsRecherche = (List<Aem>)hql.getResultList();
			
		return listeAemsRecherche;
		
	}

	@Override
	public List<Aem> getAems(Proprietaire prop) {
		
		Query hql = em.createQuery("SELECT a FROM Aem a "
								 + "INNER JOIN FETCH a.typeAem ta "
								 + "INNER JOIN FETCH a.marque "
								 + "INNER JOIN FETCH ta.categorieAem "
								 + "INNER JOIN FETCH a.proprietaire "
				//				 + "LEFT JOIN a.imgAems "
				//				 + "LEFT JOIN a.valeurs "
				//				 + "LEFT JOIN a.indisponibilites "
				//				 + "LEFT JOIN a.invisibiliteAems "
				//				 + "LEFT JOIN a.emprunts "
				//				 + "LEFT JOIN a.promotionAems "
								 + "WHERE a.proprietaire.idUtilisateur = :paramIdUtilisateur "
								 + "AND a.dateRevocation IS NULL "
								 + "AND ((SELECT COUNT (*) "
									   + "FROM InvisibiliteAem iv "
									   + "WHERE (iv.aem.idAem = a.idAem) "
									   + "AND (iv.debutInvisibiliteAem < CURRENT_DATE) "
									   + "AND (iv.finInvisibiliteAem > CURRENT_DATE) "
									   + "AND (iv.retraitInvisibiliteAem IS NULL)) = 0)");
		
		hql.setParameter("paramIdUtilisateur", prop.getIdUtilisateur());
		List<Aem> aems = (List<Aem>)hql.getResultList();
		return aems;
		
	}

	@Override
	public int addAem(Aem aem) {
		
		em.persist(aem);
		
		Query hql = em.createQuery("SELECT MAX(idAem) FROM Aem aem");
		int idAem = (Integer)hql.getSingleResult();
		return idAem;

	}

	@Override
	public int majAem(Aem aem) {

		Aem newAem = em.find(Aem.class, aem.getIdAem());
			newAem.setDateAjout(aem.getDateAjout());
			newAem.setDateRevocation(aem.getDateRevocation());
			newAem.setDescAem(aem.getDescAem());
			newAem.setEmprunts(aem.getEmprunts());
			newAem.setImgAems(aem.getImgAems());
			newAem.setIndisponibilites(aem.getIndisponibilites());
			newAem.setInvisibiliteAems(aem.getInvisibiliteAems());
			newAem.setMarque(aem.getMarque());
			newAem.setPromotionAems(aem.getPromotionAems());
			newAem.setProprietaire(aem.getProprietaire());
			newAem.setTypeAem(aem.getTypeAem());
			newAem.setValeurs(aem.getValeurs());

		em.merge(newAem);
		return aem.getIdAem();

	}

	@Override
	public int retirerAem(Aem aem) {
		
		aem = em.find(Aem.class, aem.getIdAem());
		aem.setDateRevocation(new Date());
		
		em.merge(aem);
		return aem.getIdAem();
		
	}

}
